package com.sqlcheck.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sqlcheck.entity.LLMUsageStatistics;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

@Mapper
public interface LLMUsageStatisticsRepository extends BaseMapper<LLMUsageStatistics> {

    /**
     * 根据日期和提供商查询统计信息
     */
    @Select("SELECT * FROM llm_usage_statistics WHERE date = #{date} AND llm_provider = #{provider} AND llm_model = #{model} AND hour IS NULL")
    Optional<LLMUsageStatistics> findByDateAndProviderAndModel(@Param("date") LocalDate date, 
                                                              @Param("provider") String provider, 
                                                              @Param("model") String model);

    /**
     * 根据日期和小时查询统计信息
     */
    @Select("SELECT * FROM llm_usage_statistics WHERE date = #{date} AND hour = #{hour} AND llm_provider = #{provider} AND llm_model = #{model}")
    Optional<LLMUsageStatistics> findByDateAndHourAndProviderAndModel(@Param("date") LocalDate date, 
                                                                     @Param("hour") Integer hour,
                                                                     @Param("provider") String provider, 
                                                                     @Param("model") String model);

    /**
     * 查询指定日期范围的统计信息
     */
    @Select("SELECT * FROM llm_usage_statistics WHERE date BETWEEN #{startDate} AND #{endDate} ORDER BY date DESC")
    List<LLMUsageStatistics> findByDateRange(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    /**
     * 查询提供商的总使用统计
     */
    @Select("SELECT llm_provider, llm_model, SUM(total_requests) as total_requests, " +
            "SUM(successful_requests) as successful_requests, SUM(failed_requests) as failed_requests, " +
            "AVG(avg_processing_time) as avg_processing_time " +
            "FROM llm_usage_statistics WHERE date BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY llm_provider, llm_model ORDER BY total_requests DESC")
    List<LLMUsageStatistics> getProviderSummary(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    /**
     * 根据用户ID和日期查询今日使用量
     */
    @Select("SELECT SUM(total_requests) FROM llm_usage_statistics WHERE user_id = #{userId} AND date = #{date}")
    Optional<Integer> findTodayUsageByUserId(@Param("userId") Long userId, @Param("date") LocalDate date);

    /**
     * 根据用户ID、日期、小时、提供商和模型查询统计信息
     */
    @Select("SELECT * FROM llm_usage_statistics WHERE user_id = #{userId} AND date = #{date} AND hour = #{hour} AND llm_provider = #{provider} AND llm_model = #{modelName}")
    Optional<LLMUsageStatistics> findByUserIdAndDateAndHourAndProviderAndModelName(@Param("userId") Long userId,
                                                                                   @Param("date") LocalDate date,
                                                                                   @Param("hour") Integer hour,
                                                                                   @Param("provider") String provider,
                                                                                   @Param("modelName") String modelName);
}